# -*- UTF-8 -*-


# Get data from db, answer & questions tags(If no idea, can execute sql 2 times)
import re
import time
import pandas as pd
from datetime import datetime

import pymysql


def get_user_tags(id):
    db = pymysql.connect("localhost", "root", "123456", "stackoverflow")
    cursor = db.cursor()
    # Calculate running times
    start_time = time.time()

    # Sql
    get_tags_sql = """
                SELECT CreationDate, Tags FROM questions 
                WHERE OwnerUserId = %s
                UNION
                SELECT a.CreationDate, Tags FROM answers a
                LEFT JOIN questions q ON a.ParentId = q.Id
                WHERE a.OwnerUserId = %s
                """

    cursor.execute(get_tags_sql % (id, id))
    posts = cursor.fetchall()
    # Get Every Tags
    pattern = re.compile(r"\<(.*?)\>", re.I | re.X)
    # key:period  val:tags_list
    user_dict = {}
    # record all tag type to distinct
    all_tags = []

    for post in posts:
        period_key = post[0].strftime("%Y-%m")
        period_val = []
        if period_key in user_dict.keys():
            period_val = user_dict[period_key]

        tags_str = pattern.findall(post[1])
        # print(dt, tags_list)
        for tag in tags_str:
            period_val.append(tag)
            all_tags.append(tag)

        user_dict[period_key] = period_val

    # Count users Tags and record in data frame(col：tags name ; row: every month)
    user_df = pd.DataFrame(data=0, index=user_dict.keys(), columns=list(set(all_tags)))  # use set type to distinct

    for period in user_dict.keys():
        for tag in user_dict[period]:
            user_df.loc[period, tag] += 1
    print("--- Elapse %s seconds ---" % (time.time() - start_time))

    # user_df.to_csv("user_trend.csv", sep=',', index=False)
    return user_df;


# Plot the stacked figure(%)
if __name__ == '__main__':
    get_user_tags(8687641)